using System;
using System.Data;
using System.Data.SqlClient ;

namespace UNGDUNG
{
    class clsDatabases
    {
        private string strCon="";
        private string strError = "";
        string strSQL = "";
        #region Property
        public string Error
        {
            get { return strError; }
        }
        #endregion
        #region Constructor
        public  clsDatabases()
        {
        
        }
        public  clsDatabases(string Con)
        {
            strCon = Con;
            if (Con.Equals(""))
            {
                strCon = "server=(local);";
                strCon += "database=master";
                strCon += ";uid=sa";
                strCon += ";pwd=sa;";
            }
        }
        #endregion
        public DataTable GetTable(string Option )
        {            
            switch (Option)
            { 
                case "Databases":
                    strSQL = "select name from";
                    strSQL += " master.dbo.sysdatabases";
                    strSQL += " order by name asc"; break;                
                case "Logins":
                    strSQL = "select  S.name,case isntname ";
                    strSQL += "when 1 then 'Windows Group' ";
                    strSQL += "else 'Statndard' end as Type,";
                    strSQL += "convert(char(11),createdate,103)";
                    strSQL += " as createdate,dbname,alias ";
                    strSQL += "from syslogins S,syslanguages ";
                    strSQL += " L where L.name=S.language ";
                    strSQL += " order by S.name asc"; break;
                case "Server Roles":
                    strSQL = "select 'Name' = v2.name, " +
                     "'Full Name' = v1.name from "+
                     "master.dbo.spt_values v1, "+
                     "master.dbo.spt_values v2 "+
			            "where v1.low = 0 and "+
				    "v1.type = 'SRV' and "+
				    "v2.low = -1 and v2.type = 'SRV' "+
                       "and v1.number = v2.number"; break;
                 
            }
            SqlConnection Con = new SqlConnection();
            Con.ConnectionString = strCon;
            SqlDataAdapter da  =new SqlDataAdapter(strSQL, Con);
            DataTable dt = new DataTable();
            try
            {
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                strError="Error: " + ex.Message;                    
            }
            finally
            {
                Con.Dispose();
            }
            Con.Dispose();
            return dt;
        }
        public DataTable GetTable(string Option, string Database,string type)
        {

             
            switch (Option)
            {
                case "Databases":
                    strSQL = "select name ,";
                    strSQL += "convert(char(11),crdate,103)";
                    strSQL += " as createdate,filename ";
                    strSQL += " from " + Database + ".dbo.sysdatabases";
                    strSQL += " order by name asc"; break;
                case "Tables":
                case "Views":
                case "Stored Procedure":
                    strSQL = "select O.Name,U.Name," +
                    "case xtype when 'S' then 'System'" +
                    "else 'User' end as Type," +
                    "convert(char(11),crdate ,103) as createdate " +
                    "from " + Database + ".dbo.sysobjects O, " + Database + ".dbo.sysusers U  " +
                    "where type='" + type + "' and O.uid=U.uid and category!=2"; break;
                case "Users":
                    strSQL = "select S.Name,U.name as [Login Name], " +
                   " convert(char(11),S.createdate ,103) as createdate "+
                    "from " + Database + ".dbo.sysusers S left join " +
                     "master.dbo.syslogins U  "+
                    "on U.SID=S.sid where S.status>0"; break;
                case "Roles":
                    strSQL = "select  Name,'Standard' as Type  " +
                    "from " + Database + ".dbo.sysusers where issqlrole = 1 or isapprole = 1"; break;
            }
            SqlConnection Con = new SqlConnection();
            Con.ConnectionString = strCon;
            SqlDataAdapter da = new SqlDataAdapter(strSQL, Con);
            DataTable dt = new DataTable();
            try
            {
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }
            finally
            {
                Con.Dispose();
            }
            Con.Dispose();
            return dt;
        }
        public DataTable GetTable(string Table,string Column, string Where,string orderby)
        {
            strSQL = "select " + Column ;            
            strSQL += " from " + Table + " ";
            strSQL += Where + " " + orderby; 
            SqlConnection Con = new SqlConnection();
            Con.ConnectionString = strCon;
            SqlDataAdapter da = new SqlDataAdapter(strSQL, Con);
            DataTable dt = new DataTable();
            try
            {
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }
            finally
            {
                Con.Dispose();
            }
            Con.Dispose();
            return dt;
        }
        public bool BackupDatabase(string name)
        {
            bool exist = false;
            strSQL = "select  name from master.dbo.sysdevices   ";
            strSQL += " where name = '" + name + "'";            
            SqlConnection Con = new SqlConnection();
            Con.ConnectionString = strCon;
            SqlCommand Com;
            Com = new SqlCommand(strSQL, Con);
            try
            {
                Con.Open();
                exist = (Com.ExecuteScalar() == null);
                if (exist)
                {
                    strSQL = "sp_addumpdevice 'Disk', '" + name + "', 'D:\\" + name + ".bk'";
                    Com = new SqlCommand(strSQL, Con);
                    Com.ExecuteNonQuery();
                }
                strSQL = "BACKUP DATABASE  " + name + " to " + name;
                Com = new SqlCommand(strSQL, Con);
                Com.ExecuteNonQuery();
                Con.Close();
                exist = true;
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
                if (Con.State != ConnectionState.Closed)
                {
                    Con.Close();
                }
                exist = false;
            }
            finally
            {
                Con.Dispose();
            }
            return exist;
        }
        public object[] ShowDatabase(string name)
        {
            object[] info = null; 
            strSQL = "select S.name,convert(char(11),"+
            "crdate,103) as createddate,size*10," +
            " S.filename,'PRIMARY' from sysdatabases S,"+
            " sysaltfiles F where "+
            " F.dbid=S.dbid and groupid=1 "+
            " and S.name = '" + name + "'";
            SqlConnection Con = new SqlConnection();
            Con.ConnectionString = strCon;
            SqlDataAdapter da = new SqlDataAdapter(strSQL, Con);
            DataTable dt = new DataTable();            
            try
            {
                da.Fill(dt);
                info = dt.Rows[0].ItemArray;
            }                         
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
                if (Con.State != ConnectionState.Closed)
                {                    
                    Con.Close();                     
                }              
            }
            finally
            {
                Con.Dispose();
            }
            return info;
        }
        public object[] ShowLogin(string name)
        {
            object[] info = null;
            strSQL = "select  S.name,isntname," +
           "convert(char(11),createdate,103)" +
            " as createdate,dbname,alias " +
            "from syslogins S,syslanguages " +
            " L where L.name=S.language " +
            " and S.name = '" + name + "'";             
            SqlConnection Con = new SqlConnection();
            Con.ConnectionString = strCon;
            SqlDataAdapter da = new SqlDataAdapter(strSQL, Con);
            DataTable dt = new DataTable();
            try
            {
                da.Fill(dt);
                info = dt.Rows[0].ItemArray;
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
                if (Con.State != ConnectionState.Closed)
                {
                    Con.Close();
                }
            }
            finally
            {
                Con.Dispose();
            }
            return info;
        }  
        public bool ExecuteObject(string type, string action, string name)
        {
            bool ok = false;
             
            switch (type)
            {
                case "Databases":               
                    strSQL=action+ " [" + name +"]";
                    break;
                case "Logins":
                    strSQL = action + "  " + name ;
                    break;
                
            }
            if (!strSQL.Equals("") && !name.Equals(""))
            {
                SqlConnection Con = new SqlConnection();
                Con.ConnectionString = strCon;
                SqlCommand Com = new SqlCommand(strSQL, Con);

                try
                {
                    Con.Open();
                    Com.ExecuteNonQuery();
                    ok = true;
                }
                catch (Exception ex)
                {
                    strError = "Error: " + ex.Message;
                }
                finally
                {
                    Con.Dispose();
                }
                Con.Dispose();
            }
            return ok;
        }
        public bool DropObject(string type, string action, string name)
        {
            bool ok = false;

            switch (type)
            {
                case "Databases":
                case "Tables":
                case "Views":
                case "Stored Procedure":
                    strSQL = action + " [" + name + "]";
                    break;
                case "Logins":
                    strSQL = action + "  " + name;
                    break;

            }
            if (!strSQL.Equals("") && !name.Equals(""))
            {
                SqlConnection Con = new SqlConnection();
                Con.ConnectionString = strCon;
                SqlCommand Com = new SqlCommand(strSQL, Con);

                try
                {
                    Con.Open();
                    Com.ExecuteNonQuery();
                    ok = true;
                }
                catch (Exception ex)
                {
                    strError = "Error: " + ex.Message;
                }
                finally
                {
                    Con.Dispose();
                }
                Con.Dispose();
            }
            return ok;
        }
        public bool ExecuteSQL(string SQL)
        {
            bool ok = false;
            
            if (!SQL.Equals(""))
            {
                SqlConnection Con = new SqlConnection();
                Con.ConnectionString = strCon;
                SqlCommand Com = new SqlCommand(strSQL, Con);

                try
                {
                    Con.Open();
                    Com.ExecuteNonQuery();
                    ok = true;
                }
                catch (Exception ex)
                {
                    strError = "Error: " + ex.Message;
                }
                finally
                {
                    Con.Dispose();
                }
                Con.Dispose();
            }
            return ok;
        }
        public string GetText(string database,string name)
        {
            string str = "";
            strSQL = "sp_helptext";
            if (!strSQL.Equals("") && !name.Equals(""))
            {
                SqlConnection Con = new SqlConnection();
                Con.ConnectionString = strCon;
                SqlCommand Com = new SqlCommand(strSQL, Con);
                Com.CommandType = CommandType.StoredProcedure;
                Com.Parameters.AddWithValue("@objname", name);
                try
                {                    
                    Con.Open();
                    Con.ChangeDatabase(database);
                    SqlDataReader dr = Com.ExecuteReader();
                    while (dr.Read())
                    {
                        str += dr.GetString(0);
                    }
                    dr.Dispose();
                    Con.Close();
                }
                catch (Exception ex)
                {
                    strError = "Error: " + ex.Message;
                }
                finally
                {
                    Con.Dispose();
                }
                Con.Dispose();
            }
            return str;
        }
    }
}
